import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.Scanner;


public class runUPS {
	int currentOption;
	boolean output;
	Connection conn;
	Scanner scan;

	public runUPS() {
		currentOption = 2;
		output = true;
		scan = new Scanner(System.in);
		// Load the Oracle JDBC driver
		try {
			DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

			conn = DriverManager.getConnection ("jdbc:oracle:thin:hr/hr@oracle1.cise.ufl.edu:1521:orcl", "jparker", "63EKabad%ir2");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}
	public void StartThings() {
		try {

			boolean quit = false;
			boolean special = true;
			do {


				switch (currentOption) {
				case 1:
					DoTwo();
					break;
				case 2:
					special = DoOne();
					break;
				case 3:
					output = false;
					break;
				case 4:
					output = true;
					break;
				case 5:
					quit= true;
					break;
				default:
					break;
				}
				if (special) {

				} else {
					if(output) System.out.println("(1) Query for a package route");
					if(output) System.out.println("(2) Enter a new package");
					if(output) System.out.println("(3) Turn output off");
					if(output) System.out.println("(4) Turn output on");
					if(output) System.out.println("(5) Quit");
					String scurrent = scan.nextLine();
					Integer INTcur = new Integer(scurrent);
					currentOption = INTcur.intValue();
					if (currentOption == 5) {
						quit = true;
					}
				}

			} while (!quit);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			conn.close();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}


	}

	private void DoTwo() throws SQLException {


		if(output) System.out.print("Who was the sender? ");
		String sender = scan.nextLine();
		if(output) System.out.println();

		if(output) System.out.print("What day was the package sent? ");
		String day = scan.nextLine();
		if(output) System.out.println();

		if(output) System.out.print("What time was the package sent? ");
		String time = scan.nextLine();
		if(output) System.out.println();


		getInfo(sender, day, time);
	}
	private boolean getInfo(String sender, String day, String time) throws SQLException {
		Statement statement = conn.createStatement();
		Statement statement2 = conn.createStatement();
		statement.setQueryTimeout(28);
		boolean worked = false;
		int lastmin;
		String completeday = "'" + day.substring(1, day.length()-1) + " " + time.substring(1, time.length()-1) + "'";
		String Query2 = "Select tnum, sender, receiver FROM package where sender="+ sender + " AND sent = to_date("+completeday+", 'Mon DD YYYY HH24:Mi:SS') ";
		String Query = "SELECT p.tnum, p.sender, p.receiver, to_char(p.sent, 'HH24:Mi:SS, Mon DD YYYY.'), l.id, h.fromcity, h.tocity, to_char(h.timedepart, 'HH24:Mi:SS'), to_char(l.day,'Mon DD YYYY'), h.duration  FROM package p, schedon sc, leg l, hop h WHERE p.sender= "+
				sender+" AND p.sent = to_date("+completeday+", 'Mon DD YYYY HH24:Mi:SS') AND sc.tnum = p.tnum AND h.id = sc.id AND l.id = sc.id AND l.dayint = sc.dayint ORDER BY l.dayint, h.timedepart ";
		ResultSet rs = statement.executeQuery(Query);
		if (rs.next()) {
			System.out.println("Package " + rs.getString(1) + " from " + rs.getString(2) + " to " 
					+ rs.getString(3) + " was sent at " + rs.getString(4));
			System.out.print("\n The route chose is as follows:");
			do {
				System.out.print(".\n Leg " + rs.getString(5)+ " from " + rs.getString(6) +" to "+ rs.getString(7)+ ", leaving at " + rs.getString(8) +", " + rs.getString(9));
				lastmin = rs.getInt(10);
			} while (rs.next());
			System.out.println(" and arriving "+ lastmin + " minutes later.");
			rs.close();
			statement.close();
			return true;
		} else {
			ResultSet rs2 = statement.executeQuery(Query2);
			if (rs2.next()) {
				System.out.println("Package " + rs2.getString(1) + " from " + rs2.getString(2) + " to " 
						+ rs2.getString(3) + " was sent at " + time +", " + day);
				System.out.println("There is no need to route for the package.");
				return true;
			} else {
				System.out.println("Package not found.");
				rs.close();
				statement.close();
				return false;
			}
		}
	}
	private boolean DoOne() throws Exception {
		//System.out.println("Begins");

		if(output) System.out.print("Enter the package sender (q to quit to the main menu): ");
		String sender = scan.nextLine();
		if(output) System.out.println();
		if (sender.equals("q")) {
			return false;
		}
		if(output) System.out.print("Enter the package receiver: ");
		String receiver = scan.nextLine();
		if(output) System.out.println();

		if(output) System.out.print("Enter the source city: ");
		String startCity = scan.nextLine();
		if(output) System.out.println();

		if(output) System.out.print("Enter the destination city: ");
		String endCity = scan.nextLine();
		if(output) System.out.println();

		if(output) System.out.print("Enter the package weight: ");
		String sweight = scan.nextLine();
		Integer INTweight = new Integer(sweight);
		int weight = INTweight.intValue();
		//TODO: catch non int error?
		if(output) System.out.println();

		if(output) System.out.print("What day is it? ");
		String day = scan.nextLine();

		if(output) System.out.println();

		if(output) System.out.print("What time is it? ");
		String time = scan.nextLine();
		if(output) System.out.println();


		//Checking that user entered city exists
		if (!checkCity(startCity)) {
			if(output) System.out.println("No route");
			return true;
		}
		if (!checkCity(endCity)) {
			if(output) System.out.println("No route");
			return true;
		}

		//System.out.println("Cities checked");
		//Getting connected list of cities from source to destination
		ArrayList<String> cityPlan = getCities(startCity,endCity);
		//System.out.println("got linked cities");
		//Finding nearest HOP
		MyDate tempDay = new MyDate(day.substring(1, day.length()-1)+" "+time.substring(1, time.length()-1));
		//System.out.println("DAte");
		MyGregorianCalendar currentDay = new MyGregorianCalendar();
		//System.out.println("Calendar");
		currentDay.setTime(tempDay);
		//System.out.println("calendar set");
		if (startCity.equals(endCity)) {
			createPackage(sender, receiver, currentDay.TimeToDatabase() , currentDay.DayToDatabase() , weight, currentDay.EntireToDatabase());
			if(output) System.out.println("The package will arrive at " + currentDay.TimeToDatabase() + " on " + currentDay.DayToDatabase() + ".");
			//System.out.println("There is no need to route for the package.");
			return true;
		}

		Object[] everything = calculateRoute(currentDay, cityPlan, weight);
		//System.out.println("calculated route?");
		int passed = ((Integer) everything[0]).intValue();
		if (passed != -1) {
			//System.out.println("calculated valid route");
			int tnum = createPackage(sender, receiver, ((MyGregorianCalendar) everything[4]).TimeToDatabase() , ((MyGregorianCalendar) everything[4]).DayToDatabase() , weight, currentDay.EntireToDatabase());
			//System.out.println("created package");
			scheduleLegs(tnum, (ArrayList<int[]>) everything[1]);
			updateWeight((ArrayList<int[]>) everything[1], weight);
			if(output) System.out.println("The package will arrive at " + ((MyGregorianCalendar) everything[4]).TimeToDatabase() + " on " + ((MyGregorianCalendar) everything[4]).DayToDatabase() + ".");


		} else {
			if(output) System.out.println("No route");
			return true;
		}

		//Find legs based on hop

		//Create Package in the database
		return true;



	}



	private boolean checkCity(String city) throws Exception {
		Statement statement = conn.createStatement();
		statement.setQueryTimeout(28);
		String Query = "SELECT small FROM Serves s WHERE s.small ="+city;
		ResultSet rs = statement.executeQuery(Query);
		if(rs.next()){
			rs.close();
			statement.close();
			return true;
		} else if(city.equals("'Miami'")) {
			rs.close();
			statement.close();
			return true;
		}
		return false;
	}

	private ArrayList<String> getCities(String source, String dest) throws SQLException {
		// Create a Statement
		Statement stmt = conn.createStatement();
		stmt.setQueryTimeout(28);
		Statement stmt2 = conn.createStatement();
		stmt2.setQueryTimeout(28);

		// Select the ENAME column from the EMP table
		ResultSet rset = stmt.executeQuery ("select city from table(trace("+source+")) order by i");

		// Iterate through the result and print the employee names
		ArrayList<String> cities1 = new ArrayList<String>();
		ArrayList<String> cities2 = new ArrayList<String>();
		ArrayList<String> finalcities = new ArrayList<String>();

		while (rset.next ()) {
			cities1.add(rset.getString (1));
		}

		ResultSet rset2 = stmt2.executeQuery ("select city from table(trace("+dest+")) order by i");

		while (rset2.next ()) {
			cities2.add(rset2.getString (1));
		}

		for (int i=0; i<cities1.size(); i++) {
			finalcities.add("'"+cities1.get(i)+"'");
			boolean done=false;
			for (int j=0; j<cities2.size(); j++) {

				if (cities1.get(i).equals(cities2.get(j))) {
					done = true;
					while (j>0) {

						finalcities.add("'"+cities2.get(--j)+"'");
					}
					break;
				}
			}
			if (done) break;
		}
		rset.close();
		rset2.close();
		stmt.close();
		stmt2.close();
		return finalcities;
	}

	private int createPackage(String sender, String receiver, String time, String day, int weight, String date)
	{	
		String insertString, selectString;
		insertString = "insert into Package (Sender, Receiver, TimeRec, DayRec, Weight, Sent) values("+sender+","+receiver+", to_date(?, 'HH24:MI:SS'), to_date(?, 'MM-DD-YYYY'), "+weight+",to_date('"+date+"','MM-DD-YYYY HH24:MI:SS'))";
		selectString = "select TNum from Package where Sender="+sender+" and Receiver="+receiver+" and TimeRec=to_date(?, 'HH24:MI:SS') and DayRec=to_date(?, 'MM-DD-YYYY') and Weight="+weight;
		int tnum = -1;

		try {
			PreparedStatement pstmt = conn.prepareStatement(insertString);
			pstmt.setQueryTimeout(28);
			pstmt.setString(1, time);
			pstmt.setString(2, day);
			pstmt.executeUpdate();
			pstmt.close();
			PreparedStatement pstmt2 = conn.prepareStatement(selectString);
			pstmt2.setQueryTimeout(28);
			pstmt2.setString(1, time);
			pstmt2.setString(2, day);
			ResultSet rset = pstmt2.executeQuery();

			if (rset.next())
				tnum = rset.getInt("TNum");
			pstmt2.close();
			pstmt.close();
			rset.close();

		} catch(SQLException ex) {
			System.err.println("SQLException: " + ex.getMessage());
			return -1;
		}

		return tnum;
	}


	private void scheduleLegs(int tnum, ArrayList<int[]> schedule)
	{	
		String insertString;
		insertString = "insert into schedOn (tnum, ID, dayInt) values("+tnum+",?, ?)";
		try {
			PreparedStatement pstmt = conn.prepareStatement(insertString);
			pstmt.setQueryTimeout(28);
			for (int i=0; i<schedule.size(); i++) {
				pstmt.setInt(1, schedule.get(i)[0]);
				pstmt.setInt(2, schedule.get(i)[1]);
				pstmt.executeUpdate();
			}
			//pstmt.executeBatch();
			pstmt.close();

		} catch(SQLException ex) {
			System.err.println("SQLException: " + ex.getMessage());
		}

	}

	private Object[] calculateRoute(MyGregorianCalendar currentDay, ArrayList<String> cityPlan, int weight) throws SQLException {
		MyGregorianCalendar temp = (MyGregorianCalendar) currentDay.clone();
		temp.add(Calendar.HOUR_OF_DAY, 2);
		ArrayList<String> printBlock = new ArrayList<String>();
		ArrayList<int[]> idList = new ArrayList<int[]>();
		int currentWeight;
		int id;
		int dayint;
		String line;
		String temphop;
		Object[] hop = null;
		boolean worked = true;
		for(int i = 0; i<cityPlan.size()-1; i++){
			hop = findHop(cityPlan.get(i),cityPlan.get(i+1),temp.TimeToDatabase());
			id = ((Integer) hop[0]).intValue();
			if (id != -1) {
				dayint = temp.get(Calendar.DAY_OF_MONTH);
				if (((Integer)hop[4]).intValue() == 1) {
					dayint++;
					temp.add(Calendar.DAY_OF_MONTH, 1);
				}
				if (dayint <= 10 && temp.get(Calendar.YEAR) == 2005 && temp.get(Calendar.MONTH) == 11){
					currentWeight = findLeg(id,dayint);
					if (weight + currentWeight <= ((Integer) hop[3]).intValue()){

						temphop = (String) hop[1];

						Date temp2 = new Date( dayint + " Dec 2005 "+ temphop);
						temp.set(Calendar.HOUR_OF_DAY, temp2.getHours());
						temp.set(Calendar.MINUTE, temp2.getMinutes());
						temp.set(Calendar.SECOND, temp2.getSeconds());
						//line = "Leg " + id + " from " + cityPlan.get(i)+" to " +cityPlan.get(i+1) +
						//		", leaving at " + temp.TimeToDatabase() + ", " + temp.DayToDatabase();
						//printBlock.add(line);
						idList.add(new int[]{id,dayint});
						temp.add(Calendar.MINUTE, ((Integer) hop[2]).intValue()+45);


					} else {
						//System.out.println("the weight is too much!");
						worked = false;
						break;
					}
				} else {
					//	System.out.println("The day has gone past the 10 day limit!");
					worked = false;
					break;
				}
			} else {
				//System.out.println("Wrong do something");
				worked = false;
				break;
			}
		}

		Object[] ret= new Object[5];
		if (worked){
			ret[0] = new Integer(1);
			ret[1] = idList;
			ret[2] = null; //printBlock;
			ret[3] = hop[2];
			ret[4] = temp;
		} else {
			ret[0] = new Integer(-1);
		}

		return ret;
	}
	private Object[] findHop(String city1, String city2, String time) throws SQLException{
		// TODO Auto-generated method stub
		Object[] ret = new Object[5];
		Statement stmt = conn.createStatement ();
		stmt.setQueryTimeout(28);
		// Select the ENAME column from the EMP table
		ResultSet rset = stmt.executeQuery ("select * from table(gethops("+city1+","+city2+","+"to_date('"+time+"', 'HH24:Mi:SS') ))");
		if (rset.next()) {
			ret[0] = new Integer(rset.getInt(1));
			ret[1] = new String(rset.getString(2));
			ret[2] = new Integer(rset.getInt(3));
			ret[3] = new Integer(rset.getInt(4));
			ret[4] = 0;
			rset.close();
			stmt.close();
			return ret;
		}
		else {
			rset = stmt.executeQuery ("select * from table(gethops("+city1+","+city2+","+"to_date('00:00:00', 'HH24:Mi:SS') ))");
			if (rset.next()) {
				ret[0] = new Integer(rset.getInt(1));
				ret[1] = new String(rset.getString(2));
				ret[2] = new Integer(rset.getInt(3));
				ret[3] = new Integer(rset.getInt(4));
				ret[4] = 1;
				rset.close();
				stmt.close();
				return ret;
			}
			else {
				ret[0]=new Integer(-1);
				return ret;
			}
		}
	}

	private int findLeg(int id, int dayint) throws SQLException{
		Statement stmt = conn.createStatement ();
		// Select the ENAME column from the EMP tabl
		stmt.setQueryTimeout(28);
		int ret;
		ResultSet rset = stmt.executeQuery ("select currentlbs from leg where id="+id+" and dayint="+dayint);
		if (rset.next()) {
			ret = rset.getInt("currentlbs");
			rset.close();
			stmt.close();
			return ret;
		}
		else{

			rset.close();
			stmt.close();
			return -1;
		}
	}



	private void updateWeight(ArrayList<int[]> schedule, int weight) throws SQLException{
		String insertString;
		//System.out.println("schedule size: "+ schedule.size());
		//System.out.println("first item thing? " + schedule.get(0)[0]);
		insertString = "update leg set currentlbs=currentlbs+"+weight+" where id=? and dayint=?";
		try {
			PreparedStatement pstmt = conn.prepareStatement(insertString);
			pstmt.setQueryTimeout(28);
			for (int i=0; i<schedule.size(); i++) {
				pstmt.setInt(1, schedule.get(i)[0]);
				pstmt.setInt(2, schedule.get(i)[1]);
				pstmt.executeUpdate();
			}
			//pstmt.executeBatch();
			pstmt.close();

		} catch(SQLException ex) {
			System.err.println("SQLException: " + ex.getMessage());
		}
		//System.out.println("DEBUG: Weights have been updated");
	}


}
